SUPPORT / SAMPLES & SAS NOTES
Problem Note 37033: Using the DBKEY= option in the SQL procedure might cause incorrect results
 |  |  |  |  |
Incorrect values might result in a situation where you use the DBKEY= option, the key variable contains a missing value, and the DB2 field in a DB2 table is defined as NOT NULL. In this situation, the values from the previous matched row will be retained for the non-matched row as well.
This problem can occur if you run a query similar to the following:
proc sql;
select *
from work.test a
left join db2lib.testdbkey(dbkey=(id)) b
on a.id = b.id;
quit;
This query results in the following output:
Values in SAS data set
name id
Steve .
Jan 1
Peter .
Kathy 4
Values in DB2 table
ID X
1 1
2 2
3 3
Values in result
name id X
Steve . . /* <-- Correct: X is missing here, as it should be */
Jan 1 1
Peter . 1 /* <-- Incorrect: X should be missing here, but it is not. */
Kathy 2 2
Click the Hot Fix tab in this note to access the hot fix for this issue.
Operating System and Release Information
| Product Family | Product | System | SAS Release | |
| Reported | Fixed* | |||
| SAS System | SAS/ACCESS Interface to DB2 | z/OS | 9.1 TS1M3 SP4 | 9.2 TS2M3 |